Top N and Conditional Filters in Tableau

Course: Tableau Training

Topic: Top N Filters, Bottom N Filters, Conditional Filters, Ranking, and Advanced Filtering Techniques

Learning Objectives: Understand how Top N and Conditional Filters work in Tableau, configure filters based on measures and formulas, identify top-performing products and customers, apply business conditions, and build interactive analytical dashboards.

1. Introduction to Top N and Conditional Filters

Filters in Tableau help users control which data appears in a worksheet or dashboard. Top N and Conditional Filters are dimension-filtering techniques used to focus analysis on selected members according to ranking or business rules.

• Top N Filter: Displays a specified number of highest- or lowest-ranking members of a dimension according to a measure.
• Conditional Filter: Displays dimension members that satisfy a defined condition based on a field or a formula.

For example, a sales manager may want to display the top 10 products by total sales or identify customers whose total sales exceed ₹50,000. Top N filtering handles ranking requirements, while Conditional filtering handles rule-based selection.

Both options are available in the Filter dialog for a dimension in Tableau Desktop. Read Tableau's official filtering documentation.

2. Understanding Top N Filters

A Top N Filter allows you to display only the highest-ranking or lowest-ranking members of a dimension based on a selected measure. The value N represents the number of members to retain.

For example, if a dataset contains 100 products, you can configure a Top 10 filter to show the 10 products with the highest total sales.

2.1 Examples of Top N Filters

2.2 Benefits of Top N Filters

• Identify top-performing products, customers, employees, and regions.
• Reduce large datasets to a manageable list of important members.
• Compare leading and underperforming categories.
• Build ranking dashboards and management reports.
• Support business decisions using measures such as sales, profit, quantity, and revenue.
• Combine rankings with other filters to analyze a specific business segment.

3. How to Create a Top N Filter in Tableau

1. Open Tableau Desktop and connect to a dataset, such as Sample - Superstore.
2. Create a new worksheet.
3. Drag a dimension, such as Product Name, Customer Name, or Sub-Category, to the Filters shelf.
4. In the Filter dialog box, select the Top tab.
5. Select By Field to rank members using a measure.
6. Choose Top or Bottom.
7. Enter the required number, such as 10.
8. Select the measure to rank by, such as Sales.
9. Select the aggregation, such as SUM.
10. Click OK to apply the filter.
11. Build or update the visualization to display the filtered members.

Example: To display the top 10 products by sales, place Product Name on the Filters shelf, select the Top tab, choose Top 10 by SUM(Sales), and apply the filter.

4. Understanding the Top Tab Options

When you create a dimension filter, the Top tab provides different ways to determine which members should remain in the view.

The exact fields and controls available can depend on the selected dimension and data source.

5. Top N Filter Using By Field

The By Field option is commonly used to create straightforward rankings. It lets you select the number of members, measure, and aggregation used to rank them.

5.1 Example: Top 5 Customers by Sales

Suppose the dataset contains sales for many customers. The objective is to identify the five customers who generated the highest total sales.

1. Drag Customer Name to the Filters shelf.
2. Open the Top tab.
3. Select By Field.
4. Select Top and enter 5.
5. Choose Sales as the measure.
6. Choose SUM as the aggregation.
7. Click OK.
8. Place Customer Name on Rows and SUM(Sales) on Columns.
9. Sort the bars in descending order to make the ranking easier to read.

The view displays the five customers selected by their aggregated sales ranking. If several customers have equal values around the cutoff, verify how Tableau handles the ranking in the particular view and data source.

5.2 Common Measure Choices

Important: Choose the measure and aggregation that match the business question. Top 10 by SUM(Sales) can produce a different ranking from Top 10 by AVG(Sales).

6. Understanding Bottom N Filters

A Bottom N Filter identifies the lowest-ranking members of a dimension according to a selected measure.

6.1 Example: Bottom 5 Products by Sales

A retail company wants to identify five products with the lowest total sales. Configure a Top-tab filter on Product Name and choose Bottom 5 by SUM(Sales).

The resulting view helps the business identify products that may require additional promotion, inventory review, or further investigation.

6.2 Business Applications

• Identify products with low sales.
• Find regions with the lowest profit.
• Identify customers with low purchase activity.
• Review employees or branches with low performance metrics.
• Find categories requiring additional analysis.

Note: Low performance does not automatically mean poor performance. A low-sales product may be new, seasonal, or intentionally low-volume. Interpret rankings in business context.

7. What Is a Conditional Filter?

A Conditional Filter allows you to keep or exclude members of a dimension based on a specified condition. Instead of choosing a fixed list of members manually, you define a rule that Tableau evaluates against the data.

For example, you may want to display only customers whose sales are greater than ₹50,000, or products whose average unit price is at least ₹1,000.

7.1 Common Uses of Conditional Filters

• Display customers who meet a sales target.
• Identify products with a minimum average price.
• Find categories with a specified profit level.
• Filter members according to a field-based calculation.
• Build reports based on business rules instead of manual selections.

8. Types of Conditional Filters

The Condition tab commonly provides field-based and formula-based ways to define the filtering rule.

The available controls may vary by field and data source. A condition filter on a dimension can evaluate an aggregated measure for each dimension member, so make sure the selected aggregation matches the requirement.

9. How to Create a Conditional Filter Using By Field

1. Open a worksheet in Tableau Desktop.
2. Drag a dimension, such as Customer Name, Product Name, or Sub-Category, to the Filters shelf.
3. Open the Condition tab in the Filter dialog box.
4. Select the By Field option.
5. Choose the measure that should be evaluated, such as Sales or Profit.
6. Choose an aggregation, such as SUM, AVG, MIN, or MAX.
7. Choose the appropriate comparison operator and enter the required value.
8. Click OK to apply the condition.
9. Add the dimension and relevant measure to the visualization to inspect the result.

Example: Customers with Sales Above ₹50,000

Suppose a business wants to display customers whose total sales exceed ₹50,000.

1. Drag Customer Name to Filters.
2. Open the Condition tab.
3. Select By Field.
4. Choose SUM of Sales.
5. Set the condition to greater than ₹50,000.
6. Apply the filter.

Tableau retains the customers whose aggregated sales meet the specified condition. Customers whose total sales are ₹50,000 or lower are excluded when the operator is strictly greater than.

10. How to Create a Conditional Filter Using By Formula

The By Formula option lets you write a Boolean expression that evaluates to TRUE or FALSE for each dimension member. Members satisfying the expression are retained by the filter.

10.1 Example: Customers with Total Sales Above ₹50,000

Use the following formula in the Condition tab when filtering Customer Name:

SUM([Sales]) > 50000

Explanation:

• SUM([Sales]) calculates aggregated sales at the relevant level of detail for the dimension member.
• > checks whether the value is strictly greater than the threshold.
• 50000 is the threshold value in the dataset's numeric scale.
• The expression returns TRUE for members that satisfy the condition and FALSE for members that do not.

10.2 Example: Products with Positive Total Profit

SUM([Profit]) > 0

This expression retains product members whose aggregated profit is positive, assuming the calculation is valid for the data and view.

10.3 Example: Products with Average Sales of at Least ₹1,000

AVG([Sales]) >= 1000

This expression retains members whose average sales value is greater than or equal to 1,000.

Important: These are example expressions. Use field names exactly as they appear in your Tableau data source and confirm whether the business rule should use SUM, AVG, or another aggregation.

11. Comparison Between Top N and Conditional Filters

12. Difference Between Top N and a Measure Filter

Top N filters and measure filters are related, but they answer different questions.

• Top N Filter: "Which are the top 10 products by sales?"
• Measure Filter: "Which groups have sales above ₹50,000?"

A Top N filter uses ranking to limit the number of dimension members. A measure filter retains aggregated results that satisfy a numerical criterion. A threshold might return 4 members or 40 members, while a Top 10 filter is intended to return the leading 10 members, subject to ties and the specific ranking behavior.

13. Combining Top N and Conditional Filters

Top N and Conditional settings can be used together on the same dimension filter. Tableau's dimension-filter dialog settings are cumulative, so a member must satisfy the combined filter requirements to remain in the view.

Example: Top 10 Products That Meet a Profit Condition

Business requirement: Display the top 10 products by sales, but only among products with positive total profit.

1. Drag Product Name to the Filters shelf.
2. Open the Condition tab.
3. Set a condition using SUM(Profit) greater than 0.
4. Open the Top tab.
5. Choose Top 10 by SUM(Sales).
6. Apply the filter.
7. Place Product Name on Rows and SUM(Sales) on Columns.
8. Sort the resulting chart by sales in descending order.

This setup combines a profit-based eligibility condition with a sales-based ranking. Confirm the filter's evaluation behavior and the results in the view when additional filters are involved.

14. Understanding Filter Order and Context Filters

Tableau evaluates filters according to a defined order of operations. Dimension filters, including Top and Conditional settings, are evaluated after extract, data source, and context filters, and before measure filters in the standard order.

When multiple dimension filters are applied, their interaction may affect the members available for ranking. If you need a Top N filter to rank members only within a specific subset, a context filter may be necessary.

14.1 Example: Top 10 Customers in One City

A company wants the top 10 customers by sales in New York City, rather than the top 10 customers across all cities.

1. Create a City dimension filter and select New York City.
2. Right-click the City filter on the Filters shelf.
3. Select Add to Context.
4. Create a Top 10 filter on Customer Name using SUM(Sales).
5. Review the result to confirm that the ranking is restricted to the selected city.

Making City a context filter establishes the selected city as the data subset used by the dependent Top N filter. Context filters should be used when needed for the intended filtering logic rather than added indiscriminately.

Reference: Tableau official guide to context filters.

15. Using Parameters to Make Top N Dynamic

A parameter lets a user change a value that is used by a calculation or filter. A parameter can be used in a Top N filter so that a dashboard user can select how many members to display.

Example: User-Selectable Top N Products

1. In the Data pane, create a parameter named Top N Value.
2. Set its data type to Integer.
3. Choose suitable allowable values, such as a range from 5 to 30.
4. Set a default value, such as 10.
5. Open the Top tab of the Product Name filter.
6. Choose By Field and select the parameter as the number of members, if supported by the interface.
7. Show the parameter so users can change the value.
8. Test the worksheet using several values.

The chart can now display a user-selected number of top products instead of always displaying the same fixed number. Exact controls can vary by Tableau version.

16. Practical Example Dataset

Consider the following summarized sales data:

16.1 Apply a Top 3 Filter by Sales

Choose Top 3 by SUM(Sales). The expected top three products are Laptop, Desk, and Monitor.

16.2 Apply a Conditional Filter for Positive Profit

Use the condition SUM([Profit]) > 0. Desk is excluded because its total profit is negative.

16.3 Combine the Filters

Apply the positive-profit condition and Top 3 by SUM(Sales). The eligible products are ranked by sales, and the top three among the products meeting the profit condition are retained. Based on the sample data, the expected results are Laptop, Monitor, and Printer.

Note: This is a simplified example using already summarized values. Results from a real dataset depend on the underlying records, aggregation, dimensions, and any other filters.

17. Practical Project: Product Performance Dashboard

Project Objective: Create a Tableau dashboard that identifies top-selling products and products that satisfy a profitability condition.

Step 1: Connect to the Dataset

1. Open Tableau Desktop.
2. Connect to Sample - Superstore or a sales dataset.
3. Verify that Product Name, Sales, and Profit are available.

Step 2: Create the Top N Chart

1. Open a new worksheet.
2. Drag Product Name to Rows.
3. Drag Sales to Columns.
4. Drag Product Name to the Filters shelf.
5. Open the Top tab and choose Top 10 by SUM(Sales).
6. Apply the filter.
7. Sort the chart by sales in descending order.

Step 3: Create a Conditional Filter

1. Open the filter dialog for Product Name.
2. Select the Condition tab.
3. Set a field-based condition using SUM(Profit) greater than 0, or use the equivalent Boolean formula.
4. Apply the condition.
5. Confirm that the products in the view satisfy the profit rule.

Step 4: Add Interactivity

1. Create a parameter for the Top N value if users need to change the number of products displayed.
2. Show the parameter control.
3. Add relevant filters, such as Category or Region, if required.
4. Test how the ranking changes when the selections change.

Step 5: Build the Dashboard

1. Create a new dashboard.
2. Add the product ranking chart.
3. Add a chart or summary displaying profitability.
4. Show only the filter controls that users need.
5. Add a title such as "Product Sales and Profitability Analysis".
6. Test the dashboard with different Top N values and category selections.

Expected Result

The dashboard highlights the highest-selling products and supports analysis of profitability. Users can inspect which leading products meet the selected profit condition and adjust the number of displayed products when the Top N filter is parameter-driven.

18. Business Applications

19. Common Mistakes and Troubleshooting

19.1 Using the Wrong Dimension

Problem: The Top N filter does not rank the entities you intended.

Solution: Apply the Top tab to the dimension whose members should be ranked, such as Product Name or Customer Name.

19.2 Using the Wrong Measure or Aggregation

Problem: The top products do not match the expected ranking.

Solution: Confirm whether the requirement is based on total sales, average sales, profit, or quantity. Select the appropriate measure and aggregation.

19.3 Confusing Top N with a Threshold

Problem: You want the 10 highest-ranking customers but use a condition such as SUM(Sales) greater than ₹50,000.

Solution: Use a Top N filter for ranking and a Conditional filter for a threshold-based rule.

19.4 Ignoring Ties

Problem: The number of visible members is not what you expected around the ranking cutoff.

Solution: Review the ranked values and tie behavior. If the requirement is to display exactly N rows, consider the ranking approach and how ties should be handled.

19.5 Applying the Top N Filter to the Wrong Data Subset

Problem: The top customers are ranked across the whole dataset instead of the selected city or category.

Solution: Review Tableau's filter order. A context filter may be needed to make the ranking operate on the intended subset.

19.6 Writing an Invalid Formula

Problem: Tableau displays a calculation error in the Condition tab.

Solution: Verify field names, brackets, parentheses, data types, and aggregation functions. Make sure the expression returns a Boolean result.

19.7 Applying Too Many Filters

Problem: The worksheet becomes empty or displays very few members.

Solution: Review the General, Condition, and Top tabs. Their settings are cumulative, so the remaining members must satisfy the combined requirements.

19.8 Performance Problems

Problem: Ranking or conditional filtering is slow on a large dataset.

Solution: Reduce unnecessary complexity, review data-source performance, and use context filters or alternative approaches only when they improve the required filtering logic and performance.

20. Best Practices

• Choose Top N when the requirement is to rank a specified number of members.
• Choose Conditional filtering when the requirement is based on a business rule.
• Use the correct measure and aggregation for ranking.
• Use descending sort order to make top performers easier to interpret.
• Use parameters when users need to change N dynamically.
• Review the General, Condition, and Top tabs because their settings work together.
• Use context filters when the ranking must be restricted to a specific subset of data.
• Test filters using several selections before publishing a dashboard.
• Document the ranking metric and condition threshold so users understand the result.
• Interpret rankings with business context rather than assuming that low values always indicate poor performance.

21. Interview Questions and Answers

Q1. What is a Top N Filter in Tableau?

Answer: A Top N Filter displays a specified number of highest- or lowest-ranking members of a dimension based on a selected measure or formula.

Q2. How do you create a Top 10 filter?

Answer: Drag the required dimension to the Filters shelf, open the Top tab, select By Field, choose Top 10, select the measure and aggregation, and apply the filter.

Q3. What is a Bottom N Filter?

Answer: A Bottom N Filter retains a specified number of lowest-ranking dimension members according to a selected measure or formula.

Q4. What is a Conditional Filter?

Answer: A Conditional Filter keeps dimension members that satisfy a defined condition based on a field or Boolean formula.

Q5. What is the difference between Top N and Conditional Filters?

Answer: Top N selects members by relative ranking, while Conditional filtering selects members that meet a specified rule or threshold.

Q6. What is the difference between By Field and By Formula?

Answer: By Field uses a selected measure, aggregation, and comparison settings to define the condition or ranking. By Formula uses an expression to evaluate members or rank them, depending on the filter tab.

Q7. Can Top N and Conditional filters be used together?

Answer: Yes. Their settings can be combined on a dimension filter. The resulting members must satisfy the combined filtering requirements.

Q8. Why might a Top N filter return unexpected customers?

Answer: The wrong measure or aggregation may have been selected, another filter may be affecting the results, or the ranking may be calculated across a different data subset than intended.

Q9. What is a context filter, and why is it useful for Top N?

Answer: A context filter establishes a primary subset of data. It can make a dependent Top N filter rank members within that subset, such as the top customers in a selected city.

Q10. How can a user change the number of displayed products?

Answer: A parameter can be used to control the N value in a Top N filter, where supported by the Tableau interface and configuration.

Q11. Can a Conditional Filter use SUM(Profit)?

Answer: Yes. For example, SUM([Profit]) > 0 can retain members whose aggregated profit is positive.

Q12. Why is a Top N filter different from a measure filter?

Answer: Top N ranks members and limits the number selected, whereas a measure filter applies a numerical condition to aggregated values at the level of detail of the view.

22. Practice Exercises

1. Create a Top 10 Products by Sales worksheet.
2. Create a Bottom 5 Customers by Profit worksheet.
3. Filter customers whose SUM(Sales) is greater than ₹50,000.
4. Filter products whose SUM(Profit) is greater than 0.
5. Use AVG(Sales) to create a different Top 10 ranking and compare it with SUM(Sales).
6. Combine a positive-profit condition with a Top 10 by Sales filter.
7. Create a Top N parameter so users can choose the number of products displayed.
8. Apply a Category context filter and find the top products within the selected category.
9. Test the impact of adding a Region filter to an existing Top N view.
10. Explain when you would use a threshold condition instead of a Top N filter.

23. Quick Revision Checklist

• Understand the purpose of Top N and Bottom N filters.
• Know how to configure the Top tab in a dimension filter.
• Understand the By Field and By Formula options.
• Know how to create a Conditional Filter using a field or formula.
• Understand how aggregation affects ranking and conditions.
• Know the difference between ranking and a numerical threshold.
• Understand how General, Condition, and Top settings interact.
• Know when a context filter is useful for Top N analysis.
• Understand how parameters can make Top N dynamic.
• Practice building ranking and performance dashboards.

24. Conclusion

Top N and Conditional Filters are important Tableau features for ranking data and applying business rules. Top N filters help identify the highest- or lowest-performing members, while Conditional Filters retain members that satisfy specified conditions. Combining them allows analysts to create focused views, such as the top 10 profitable products or the highest-selling customers within a particular region.

Practice these filters with sample datasets, check the selected aggregation, and understand the impact of other filters before interpreting rankings or sharing dashboards.

25. Learn Tableau with JustAcademy

Explore Tableau training to strengthen your skills in data visualization, filtering, dashboard development, reporting, and business analytics.

JustAcademy Tableau Training

Interested in learning more? Register for a course demo to explore the training before enrolling.

Register for a Tableau Course Demo

26. Additional Learning Resources

• Tableau Official Help: Filter Data from Your Views
• Tableau Official Help: Order of Operations
• Tableau Official Help: Context Filters